

-- Project: SQL Presidents Data
-- Description:
	-- This SQL program is about the data for each president of the U.S.
	-- This data is used to see any things, such as the number of terms in office.
	-- Also, is shows when each president served.
	-- This data can be used to create many tables to organize it and see how it compares to each other.
-- Jamila Kandawala
-- Date: 1/7/2025

create database Presidents; -- This is creating a database that will be called Presidents, which will allow you to create a table in it.

use Presidents; -- This will allow you to use the Presidents database and out in any tables needed.

-- This will allow you to create a table with columns that will allow you to then put in data.
create table presidentsData(
	presidentialNumber INT PRIMARY KEY, -- This will make the presidential number be the primary key.
	lastName varchar(20), -- This is creating a field called lastName, which was allow 20 characters.
	firstName varchar(20), -- This is creating a field called firstname, which was allow 20 characters.
	party varchar(50), -- This is creating a field called party, which was allow 50 characters.
	nickname varchar(50), -- This is creating a field called nickname, which was allow 50 characters.
	birthYear varchar(5), -- This is creating a field called birthYear, which was allow 5 characters.
	deathYear varchar(5), -- This is creating a field called deathYear, which was allow 5 characters.
	termInOffice varchar(5), -- This is creating a field called termInOffice, which was allow 5 characters.
	decade varchar(5), -- This is creating a field called decade, which was allow 5 characters.
	otherPositions varchar(255), -- This is creating a field called otherPositions, which was allow 255 characters.
	majorAccom varchar(255), -- This is creating a field called majorAccom, which was allow 255 characters.
	warsDuringOffice varchar(80) -- This is creating a field called warsDuringOffice, which was allow 80 characters.
);


create index idx_lastName on presidentsData(lastName); -- This is creating an index for the lastName field, so that it would say MUL under the KEY column.

create index idx_party on presidentsData(party); -- This is creating an index for the party field, so that it would say MUL under the KEY column.

describe presidentsData; -- This will describe the presidents data table and will allow get the information of each other columns, so it will allow us to see a table of the columns.


insert into presidentsData -- This will allow you to put in all the data for each field into the table.
-- These are the field names, which will allow you to order the data into it for each president. 
(presidentialNumber, lastName, firstName, party, nickname, birthYear, deathYear, termInOffice, decade, otherPositions, majorAccom, warsDuringOffice)

VALUES -- This shows that every line after will be the data, which will be put into the columns
-- This is all the data for each president in the United States and the columns will give specific data in it, which will be organized in the table.
('1', 'Washington', 'George', 'Federalist', 'The American Cincinnatus', '1732', '1799', '2', '1790s', 'Commander-in-Chief of the Continental Army', 'Led the Continental Army to victory in the American Revolutionary War/First President of the United States', 'None'),
('2', 'Adams', 'John', 'Federalist', 'The Colossus of Independence', '1735', '1826', '1', '1790s', 'Vice President under George Washington', 'Played a key role in the American Revolution/Helped draft the Declaration of Independence', 'Quasi-War'),
('3', 'Thomas', 'Jefferson', 'Democratic-Republican', 'The Apostle of Democracy', '1743', '1826', '2', '1800s', 'Vice President under John Adams/Secretary of State', 'Authored the Declaration of Independence/Completed the Louisiana Purchase', 'First Barbary War'),
('4', 'Madison', 'James', 'Democratic-Republican', 'Little Jemmy', '1751', '1836', '2', '1810s', 'Secretary of State under Thomas Jefferson', 'Known as the "Father of the Constitution"/Led the nation during the War of 1812', 'War of 1812'),
('5', 'Monroe', 'James', 'Democratic-Republican', 'The Era of Good Feelings President', '1758', '1831', '2', '1820s', 'Secretary of State/Secretary of War under James Madison', 'Established the Monroe Doctrine/A key element of U.S. foreign policy', 'First Seminole War'),
('6', 'Adams', 'John Quincy', 'Republican', 'Old Man Eloquent', '1767', '1848', '1', '1820s', 'Secretary of State under James Monroe', 'Advocated for the abolition of slavery', 'None'),
('7', 'Jackson', 'Andrew', 'Democrat', 'Old Hickory', '1767', '1845', '2', '1830s', 'U.S. Senator from Tennessee/Military governor of Florida', 'Role in the Battle of New Orleans/His presidencys populist policies', 'Second Seminole War'),
('8', 'Buren', 'Martin Van', 'Democrat', 'The American Talleyrand', '1782', '1862', '1', '1830s', 'Vice President under Andrew Jackson/Secretary of State', 'Played a key role in the formation of the Democratic Party', 'None'),
('9', 'Harrison', 'William Henry', 'Whig', 'General Mum', '1773', '1841', '1 mon', '1840s', 'U.S. Minister to Colombia/Military Officer', 'Known for his military leadership in the Battle of Tippecanoe', 'None'),
('10', 'Tyler', 'John', 'Whig', 'His Accidency', '1790', '1862', '1', '1840s', 'Vice President under William Henry Harrison/U.S. Senator from Virginia', 'Annexed Texas into the United States', 'None'),
('11', 'Polk', 'James K.', 'Democrat', 'Napoleon of the Stump', '1795', '1849', '1', '1840s', 'Governor of Tennessee/Speaker of the House of Representatives', 'Expanded U.S. territory through the Mexican-American War', 'Mexican-American War'),
('12', 'Taylor', 'Zachary', 'Whig', 'Old Rough and Ready', '1784', '1850', '1', '1840s', 'Major General in the U.S. Army', 'A hero of the Mexican-American War', 'None'),
('13', 'Fillmore', 'Milard', 'Whig', 'The American Louis Philippe', '1800', '1874', '1', '1850s', 'Vice President under Zachary Taylor/U.S. Representative from New York', 'Supported the Compromise of 1850', 'None'),
('14', 'Pierce', 'Franklin', 'Democrat', 'Young Hickory of the Granite Hills', '1804', '1869', '1', '1850s', 'U.S. Senator from New Hampshire', 'Signed the Kansas-Nebraska Act', 'None'),
('15', 'Buchanan', 'James', 'Democrat', 'Old Public Functionary', '1791', '1868', '1', '1850s', 'Secretary of State under James K. Polk', 'Dred Scott decision', 'None'),
('16', 'Lincoln', 'Abraham', 'Republican', 'The Great Emancipator', '1809', '1865', '1', '1860s', 'U.S. Representative from Illinois/Lawyer', 'Led the nation through the Civil War/Issued the Emancipation Proclamation', 'American Civil War'),
('17', 'Johnson', 'Andrew', 'Democrat', 'The Tennessee Tailor', '1808', '1875', '1', '1860s', 'Vice President under Abraham Lincoln/Military governor of Tennessee', 'Oversaw the early years of Reconstruction', 'None'),
('18', 'Grant', 'Ulysses S.', 'Republican', 'Unconditional Surrender Grant', '1822', '1885', '2', '1870s', 'Commanding General of the U.S. Army', 'Commanded Union forces to victory in the Civil War/Worked to protect African American rights during Reconstruction', 'Indian Wars'),
('19', 'Hayes', 'Rutherford B.', 'Republican', 'Rutherfraud', '1822', '1893', '1', '1870s', 'Governor of Ohio/U.S. Representative from Ohio', 'Ended Reconstruction/Promoted civil service reform', 'NoneNone'),
('20', 'Garfield', 'James A.', 'Republican', 'Boatman Jim', '1831', '1881', '1', '1880s', 'U.S. Representative from Ohio/Major General in the Union Army', 'Advocated for civil service reform', 'None'),
('21', 'Arthur', 'Chester A.', 'Republican', 'Gentleman Boss', '1829', '1886', '1', '1880s', 'Vice President under James A. Garfield', 'Signed the Pendleton Civil Service Reform Act', 'None'),
('22', 'Cleveland', 'Grover', 'Democrat', 'Uncle Jumbo', '1837', '1908', '1', '1880s', 'Governor of New York/Mayor of Buffalo', 'Known for his honesty and fight against corruption', 'None'),
('23', 'Harrison', 'Benjamin', 'Republican', 'The Front Porch Campaigner', '1833', '1901', '1', '1890s', 'U.S. Senator from Indiana/Brigadier General in the Union Army', 'Signed the Sherman Antitrust Act', 'None'),
('24', 'Cleveland', 'Grover', 'Democrat', 'Uncle Jumbo', '1837', '1908', '1', '1890s', 'Governor of New York/Mayor of Buffalo', 'Known for his honesty and fight against corruption', 'None'),
('25', 'McKinley', 'William', 'Republican', 'The Napoleon of Protection', '1843', '1901', '1', '1890s', 'Governor of Ohio/U.S. Representative from Ohio', 'Led the nation during the Spanish-American War', 'Spanish-American War'),
('26', 'Roosevelt', 'Theodore', 'Republican', 'The Trust Buster', '1858', '1919', '2', '1900s', 'Vice President under William McKinley/Governor of New York', 'Known for his progressive policies/Conservation efforts', 'Philippine-American War'),
('27', 'Taft', 'William Howard', 'Republican', 'Big Chief', '1857', '1930', '1', '1910s', 'Secretary of War under Theodore Roosevelt', 'Strengthened antitrust laws', 'None'),
('28', 'Wilson', 'Woodrow', 'Democrat', 'The Phrasemaker', '1857', '1924', '2', '1910s', 'Governor of New Jersey', 'Led the nation during World War I/Promoted the League of Nations.', 'World War 1'),
('29', 'Harding', 'Warren G.', 'Republican', 'Wobbly Warren', '1865', '1923', '1', '1920s', 'U.S. Senator/Lieutenant from Ohio', 'Advocated for a "return to normalcy" after World War I', 'None'),
('30', 'Coolidge', 'Calvin', 'Republican', 'Cautious Cal', '1872', '1933', '2', '1920s', 'Vice President under Warren G. Harding/Governor of Massachusetts', 'Known for his laissez-faire economic policies', 'None'),
('31', 'Hoover', 'Herbert', 'Republican', 'The Great Humanitarian', '1874', '1964', '1', '1930s', 'Secretary of Commerce under Warren G. Harding and Calvin Coolidge', 'Led humanitarian efforts after World War I', 'None'),
('32', 'Roosevelt', 'Franklin D.', 'Democrat', 'That Man in the White House', '1882', '1945', '3', '1940s', 'Assistant Secretary of the Navy', 'Led the nation through the Great Depression/World War II', 'World War 2'),
('33', 'Truman', 'Harry S.', 'Democrat', 'Give Em Hell Harry', '1884', '1972', '2', '1940s', 'Vice President under Franklin D. Roosevelt', 'Made the decision to use atomic bombs in World War II/Established the Truman Doctrine', 'Korean War'),
('34', 'Eisenhower', 'Dwight D.', 'Republican', 'Ike', '1890', '1969', '2', '1950s', 'Supreme Commander of the Allied Expeditionary Forces', 'Led the Allied forces in Europe during World War II through military', 'None'),
('35', 'Kennedy', 'John F.', 'Democrat', 'Jack', '1917', '1963', '1', '1960s', 'U.S. Representative from Massachusetts', 'Known for the Cuban Missile Crisis', 'None'),
('36', 'Johnson', 'Lyndon B.', 'Democrat', 'Landslide Lyndon', '1908', '1973', '1', '1960s', 'Vice President under John F. Kennedy', 'Signed the Civil Rights Act/Promoted the Great Society programs', 'Vietnam War'),
('37', 'Nixon', 'Richard M.', 'Republican', 'Tricky Dick', '1913', '1994', '1', '1970s', 'Vice President under Dwight D. Eisenhower/U.S. Senator from California', 'Opened diplomatic relations with China', 'Vietnam War'),
('38', 'Ford', 'Gerald R.', 'Republican', 'Mr. Nice Guy', '1913', '2006', '1', '1970s', 'Vice President under Richard Nixon/U.S. Representative from Michigan', 'Pardoned Richard Nixon/Promoted economic policies to combat inflation', 'None'),
('39', 'Carter', 'Jimmy', 'Democrat', 'The Peanut Farmer', '1924', '2024', '1', '1970s', 'Governor/State Senator of Georgia', 'Brokered the Camp David Accords', 'None'),
('40', 'Reagan', 'Ronald', 'Republican', 'Dutch', '1911', '2004', '2', '1980s', 'Governor of California', 'Known for his economic policies and ending the Cold War', 'Invasion of Grenada'),
('41', 'Bush', 'George', 'Republican', 'Bush Senior', '1924', '2018', '1', '1990s', 'Vice President under Ronald Reagan/Director of Central Intelligence', 'Led the nation during the Gulf War', 'Gulf War'),
('42', 'Clinton', 'Bill', 'Democrat', 'Slick Willie', '1946', 'Alive', '2', '1990s', 'Governor/Attorney General of Arkansas', 'Promoted economic growth/Balanced the federal budget', 'Kosovo War'),
('43', 'Bush', 'George W.', 'Republican', 'Bush Jr.', '1946', 'Alive', '2', '2000s', 'Governor of Texas/Businessman', 'Led the nation during the 9/11 attacks/Initiated the War on Terror', 'War in Afghanistan and Iraq War'),
('44', 'Obama', 'Barack', 'Democrat', 'No Drama Obama', '1961', 'Alive', '2', '2010s', 'U.S. Senator/State Senator from Illinois', 'Passed the Affordable Care Act/Promoted economic recovery', 'War in Afghanistan and Iraq War'),
('45', 'Trump', 'Donald', 'Republican', 'President Snowflake', '1946', 'Alive', '1', '2010s', 'Businessman/Television personality', 'Promoted tax reform and deregulation', 'Russia and Ukraine War'),
('46', 'Biden', 'Joe', 'Democrat', 'Scranton Joe', '1942', 'Alive', '1', '2020s', 'Vice President under Barack Obama/U.S. Senator from Delaware', 'Promoted COVID-19 relief efforts and infrastructure investment', 'War in Afghanistan and Russia/Ukraine War')
;


Select Concat(presidentialNumber, " ", firstName, " ", lastName) AS 'Presidential Number, Name' -- This is selecting all the data in the firstName and lastName columns in order to orgainze it.
From presidentsData -- This will get all the data in the columns from the presidentsData, so the computer knows where to get the data from.
Where firstName Like "James%"; -- This will use the data and organize it by the firstname being James and will only select that data. 

Select Concat(presidentialNumber, " ", firstName, " ", lastName, " ", party) AS 'Presidential Number, Name, Party' -- This is selecting all the data in the firstName, lastName, and party columns in order to orgainze it.
From presidentsData -- This will get all the data in the columns from the presidentsData, so the computer knows where to get the data from.
Where party = "Democrat"; -- This will get the data from these columns and it will display the data where the presidents party is democrat.

Select Concat(presidentialNumber, " ", firstName, " ", lastName, " ", party) AS 'Presidential Number, Name, Party' -- This is selecting all the data in the firstName, lastName, and party columns in order to orgainze it.
From presidentsData -- This will get all the data in the columns from the presidentsData, so the computer knows where to get the data from.
Where party = "Republican"; -- This will get the data from these columns and it will display the data where the presidents party is republican.

Select Concat(presidentialNumber, " ", firstName, " ", lastName, " ", party) AS 'Presidential Number, Name, Party' -- This is selecting all the data in the firstName, lastName, and party columns in order to orgainze it.
From presidentsData -- This will get all the data in the columns from the presidentsData, so the computer knows where to get the data from.
Where party != "Republican" AND party != "Democrat"; -- This will get the data from these columns and it will display the data where the presidents party is not republican and democrat.

Select Concat(presidentialNumber, " ", firstName, " ", lastName) AS 'Presidential Number, Name' -- This is selecting all the data in the firstName and lastName in order to orgainze it.
From presidentsData -- This will get all the data in the columns from the presidentsData, so the computer knows where to get the data from.
Where lastName Like "M%"; -- This will get the data and display the lastName of the president that starts with M.

Select Concat(presidentialNumber, " ", firstName, " ", lastName, " ", termInOffice) AS 'Presidential Number, Name, Number of Term(s) Served' -- This is selecting all the data in the firstName, lastName, and termInOffice columns in order to orgainze it.
From presidentsData -- This will get all the data in the columns from the presidentsData, so the computer knows where to get the data from.
Where termInOffice = "1"; -- This will get the data with the term of the presidents in the office that is equal to 1. 

Select Concat(presidentialNumber, " ", firstName, " ", lastName, " ", termInOffice) AS 'Presidential Number, Name, Number of Term(s) Served' -- This is selecting all the data in the firstName, lastName, and termInOffice columns in order to orgainze it.
From presidentsData -- This will get all the data in the columns from the presidentsData, so the computer knows where to get the data from.
Where termInOffice = "2"; -- This will get the data of the president that the term of the office will be equal to 2.

Select Concat(presidentialNumber, " ", firstName, " ", lastName, " ", termInOffice) AS 'Presidential Number, Name, Number of Term(s) Served' -- This is selecting all the data in the firstName, lastName, and termInOffice columns in order to orgainze it.
From presidentsData -- This will get all the data in the columns from the presidentsData, so the computer knows where to get the data from.
Where termInOffice > "2"; -- This will get the data of the preisdent and the term of office that is more than 2.

Select Concat(presidentialNumber, " ", firstName, " ", lastName) AS 'Presidential Number, Name'  -- This is selecting all the data in the firstName and lastName columns in order to orgainze it.
From presidentsData -- This will get all the data in the columns from the presidentsData, so the computer knows where to get the data from.
Where warsDuringOffice = "None"; -- This will display the presidents term that during the term there was no wars.

drop database Presidents; -- This will delete the Presidents database, which will also delete the table and all the data with it.
